package com.uniview.service.report.impl;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang3.StringUtils;
import org.hibernate.SessionFactory;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.uniview.dao.EmployeesDao;
import com.uniview.dao.ServiceOrdersDao;
import com.uniview.entity.po.RegionManager;
import com.uniview.entity.vo.Pager;
import com.uniview.entity.vo.WorkingHoursReport;
import com.uniview.service.RegionManagerService;
import com.uniview.service.report.WorkingHoursReportService;

@Service("workingHoursReoprtService")
public class WorkingHoursReportServiceImpl implements WorkingHoursReportService {

	@Autowired
	private RegionManagerService regionManagerService;

	@Autowired
	private EmployeesDao employeesDao;

	@Autowired
	private ServiceOrdersDao ordersDao;

	@Resource
	private SessionFactory sessionFactory;

	@Override
	@SuppressWarnings("unchecked")
	public Pager<WorkingHoursReport> findWorkingHoursReoprtByPage(String regionName, String startTime, String endTime,
			int pageSize, int pageNo) {
		Pager<WorkingHoursReport> reportPager = new Pager<>();
		try {
			// Pager<RegionManager> pager =
			// regionManagerService.findRegionByPage(regionName, pageSize, pageNo);
			Pager<RegionManager> pager = regionManagerService.findRegionByPageBySingle(regionName, pageSize, pageNo);
			List<RegionManager> list = pager.getPagerList();
			List<WorkingHoursReport> workingHoursReoprts = new ArrayList<>();
			reportPager.setTotalRow(pager.getTotalRow());
			String query = "";
			String queryassortment = "";
			if (StringUtils.isNotBlank(startTime) && StringUtils.isNotBlank(endTime)) {
				query = "  and so.endTime BETWEEN  TO_DATE('" + startTime + " 00:00:00" + "', 'yyyy-MM-dd hh24:mi:ss') and TO_DATE('" + endTime
						+ " 23:59:59" + "', 'yyyy-MM-dd hh24:mi:ss') ";
				queryassortment = "  and o2.endTime BETWEEN  TO_DATE('" + startTime + " 00:00:00" + "', 'yyyy-MM-dd hh24:mi:ss') and TO_DATE('" + endTime
						+ " 23:59:59" + "', 'yyyy-MM-dd hh24:mi:ss') ";
			}
			Map<String, Object> map = new HashMap<>();
			List<String> single = new ArrayList<String>();
			for (int i = 0; i < list.size(); i++) {
				String orgname = list.get(i).getRegionName();
				// 重复办事处只做一次统计，一个办事处可能会有多个服务经理
				if (!single.contains(orgname)) {
					single.add(orgname); // 记录已统计过的部门
					// 统计总人数
					String sql = "SELECT count(*) as peoples from SD_EMPLOYEES where orgname like '%" + orgname + "%'";
					Integer peopleNum = employeesDao.findTotalRowBySql(sql);
					WorkingHoursReport report = new WorkingHoursReport(orgname, peopleNum, 0, 0, 0, 0, 0.00f);
					if (peopleNum > 0) {
						// 统计总工时、总工单数
						sql = "SELECT  NVL(sum(so.workhours),0) as orderHours,count(so.handlerDept) as orderNums FROM SD_ORDERS so  WHERE  so.status <> -99 and so.handlerDept like '%"
								+ orgname + "%' " + query + " ";
						List datas = sessionFactory.getCurrentSession().createSQLQuery(sql)
								.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
						// 满意度
						sql = "SELECT count(*)  FROM SD_ORDERS so  WHERE  so.status <> -99 and so.handlerDept like '%" + orgname
								+ "%' AND so.satisfaction=1 " + query + " ";
						Integer stCount = ordersDao.findTotalRowBySql(sql);

						// 分类统计
						sql = "select * from (select nvl(sum(o2.workhours),0) as c1 , 1 as sorter from sd_orders o2 where o2.status <> -99 and o2.orderfrom like '项目交付%' and o2.handlerdept like '%"
								+ orgname + "%'"
								+ queryassortment + " "
								+ "union select nvl(sum(o2.workhours),0) as c1, 2 as sorter from sd_orders o2 where o2.status <> -99 and o2.orderfrom like '对外测试%' and o2.handlerdept like '%"
								+ orgname + "%'"
								+ queryassortment + " "
								+ "union select nvl(sum(o2.workhours),0) as c1, 3 as sorter from sd_orders o2 where o2.status <> -99 and o2.orderfrom like '备件维修%' and o2.handlerdept like '%"
								+ orgname + "%'"
								+ queryassortment + " "
								+ "union select nvl(sum(o2.workhours),0) as c1, 4 as sorter from sd_orders o2 where o2.status <> -99 and o2.orderfrom like '网上问题%' and o2.handlerdept like '%"
								+ orgname + "%'"
								+ queryassortment + " "
								+ "union select nvl(sum(o2.workhours),0) as c1, 5 as sorter from sd_orders o2 where o2.status <> -99 and o2.orderfrom like '其它%' and o2.handlerdept like '%"
								+ orgname + "%'"
								+ queryassortment + " "
								+ "union select nvl(sum(o2.workhours),0) as c1, 6 as sorter from sd_orders o2 where o2.status <> -99 and o2.orderfrom like '培训%' and o2.handlerdept like '%"
								+ orgname + "%'" 
								+ queryassortment + " "
								+ ") order by sorter" + "";
						List clazzifyList = sessionFactory.getCurrentSession().createSQLQuery(sql)
								.setResultTransformer(Transformers.TO_LIST).list();
						// 项目交付
						List vals = (List) clazzifyList.get(0);
						float sumHours = ((BigDecimal) vals.get(0)).floatValue();
						report.setProjectDeliveryHours(Math.round((sumHours * 100)) / 100.00f);
						// 对外测试
						vals = (List) clazzifyList.get(1);
						sumHours = ((BigDecimal) vals.get(0)).floatValue();
						report.setExternalTestingHours(Math.round((sumHours * 100)) / 100.00f);
						// 备件维修
						vals = (List) clazzifyList.get(2);
						sumHours = ((BigDecimal) vals.get(0)).floatValue();
						report.setSparePartsHours(Math.round((sumHours * 100)) / 100.00f);
						// 网上问题
						vals = (List) clazzifyList.get(3);
						sumHours = ((BigDecimal) vals.get(0)).floatValue();
						report.setOnlineProblemHours(Math.round((sumHours * 100)) / 100.00f);
						// 其他
						vals = (List) clazzifyList.get(4);
						sumHours = ((BigDecimal) vals.get(0)).floatValue();
						report.setOtherHours(Math.round((sumHours * 100)) / 100.00f);
						// 培训
						vals = (List) clazzifyList.get(5);
						sumHours = ((BigDecimal) vals.get(0)).floatValue();
						report.setTrainHours(Math.round((sumHours * 100)) / 100.00f);

						if (datas.size() > 0) {
							map = (Map<String, Object>) datas.get(0);
							report.setOrderHours(Float.parseFloat(String.valueOf(map.get("ORDERHOURS"))));
							report.setOrderNums(Integer.parseInt(String.valueOf(map.get("ORDERNUMS"))));
							report.setAverageHours((float) report.getOrderHours() / peopleNum);
							report.setAverageOrders((float) report.getOrderNums() / peopleNum);
							if (report.getOrderNums() != null && report.getOrderNums() != 0) {
								double f = new BigDecimal((float) stCount / report.getOrderNums())
										.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
								report.setSatisfaction((float) f);
							}
						}
					}
					workingHoursReoprts.add(report);
				}
			}
			reportPager.setPagerList(workingHoursReoprts);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return reportPager;

	}

	@SuppressWarnings("unchecked")
	@Override
	public List<WorkingHoursReport> findWorkingHoursReoprt(String regionName, String startTime, String endTime) {
		List<WorkingHoursReport> workingHoursReoprts = new ArrayList<>();
		try {
			List<RegionManager> list = regionManagerService.findAllRegion(regionName);
			String query = "";
			if (StringUtils.isNotBlank(startTime) && StringUtils.isNotBlank(endTime)) {
				query = "  and so.endTime BETWEEN  TO_DATE('" + startTime + " 00:00:00" + "', 'yyyy-MM-dd hh24:mi:ss') and TO_DATE('" + endTime
						+ " 23:59:59" + "', 'yyyy-MM-dd hh24:mi:ss') ";
			}
			Map<String, Object> map = new HashMap<>();
			for (int i = 0; i < list.size(); i++) {
				String orgname = list.get(i).getRegionName();
				// 统计总人数
				String sql = "SELECT count(*) as peoples from SD_EMPLOYEES where orgname like '%" + orgname + "%'";
				Integer peopleNum = employeesDao.findTotalRowBySql(sql);
				WorkingHoursReport report = new WorkingHoursReport(orgname, peopleNum, 0, 0, 0, 0, 0.00f);
				if (peopleNum > 0) {
					// 统计总工时、总工单数
					sql = "SELECT  NVL(sum(so.workhours),0) as orderHours,count(so.handlerDept) as orderNums FROM SD_ORDERS so  WHERE so.status <> -99 and so.handlerDept like '%"
							+ orgname + "%' " + query + " ";
					List datas = sessionFactory.getCurrentSession().createSQLQuery(sql)
							.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
					// 满意度
					sql = "SELECT count(*)  FROM SD_ORDERS so WHERE so.status <> -99 and so.handlerDept like '%" + orgname
							+ "%' AND so.satisfaction=1 " + query + " ";
					Integer stCount = ordersDao.findTotalRowBySql(sql);

					if (datas.size() > 0) {
						map = (Map<String, Object>) datas.get(0);
						report.setOrderHours(Float.parseFloat(String.valueOf(map.get("ORDERHOURS"))));
						report.setOrderNums(Integer.parseInt(String.valueOf(map.get("ORDERNUMS"))));
						report.setAverageHours((float) report.getOrderHours() / peopleNum);
						report.setAverageOrders((float) report.getOrderNums() / peopleNum);
						if (report.getOrderNums() != null && report.getOrderNums() != 0) {
							double f = new BigDecimal((float) stCount / report.getOrderNums())
									.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
							report.setSatisfaction((float) f);
						}
					}
				}
				workingHoursReoprts.add(report);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return workingHoursReoprts;
	}

}
